package com.yenlien.traceyun.plant.dao;

import com.alibaba.fastjson.JSONObject;
import com.yenlien.traceyun.plant.entity.Reap;
import com.yenlien.traceyun.webase.dao.BaseJpaDao;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

public interface ReapDao extends BaseJpaDao<Reap> {

    Reap findById(String id);

    List<Reap> findByPlantBatch(String plantBatch);


    List<Reap> findByCompanyId(String companyId);


    @Query(value = "SELECT reap_status FROM t_plant_reap WHERE plant_batch = ?1", nativeQuery = true)
    int getStatusByPlantBatch(String plantBatch);

    //采购数量统计
    @Query(value = "SELECT sum(d.reap_number) FROM t_plant_reap r LEFT JOIN t_plant_reap_detail d ON r.id = d.reap_id WHERE 1 = 1 AND r.company_id =?1", nativeQuery = true)
    double purchaseCount(String companyId);

    // 获取未上传中央平台的采收数据
    @Query(value = "SELECT tpr.* FROM t_plant_reap tpr JOIN t_company tc ON tpr.company_id = tc.id LEFT JOIN dock_zhongyang dz ON tpr.id = dz.harvest_plant_id WHERE " +
            "dz.id IS NULL AND ISNULL( tc.credit_code ) = 0  AND LENGTH( trim( tc.credit_code ) ) >0 AND tc.id = \"2c9d0ded78ca2e170178cf33f82b0099\"", nativeQuery = true)
    List<Reap> getSnPlantHarvest();


    //	@Query(value = "SELECT reap_number,units FROM t_plant_reap_detail WHERE company_id =?1", nativeQuery = true)
    @Query(value = "SELECT d.reap_number,d.units FROM t_plant_reap r LEFT JOIN t_plant_reap_detail d ON r.id = d.reap_id WHERE 1 = 1 AND r.company_id =?1", nativeQuery = true)
    List<Map<String, Object>> purchaseCount2(String companyId);



    @Query(value = "SELECT tc.id, tc.credit_code, tprd.reap_part herbal_part, tprd.batch `code`, tpr.begin_time start_time, tpr.end_time, IF(tpr.reap_way = '机械', 2, 1) excavation_method, " +
            "tpr.reap_area area, tprd.reap_number amount FROM t_plant_reap tpr LEFT JOIN t_plant_reap_detail tprd ON tpr.id = tprd.reap_id LEFT JOIN t_company tc ON tpr.company_id = tc.id " +
            "WHERE tc.parent_id IN (?1) AND tc.dock_province = '1'", nativeQuery = true)
    List<JSONObject> dock3(List<String> parentIdList);


    // plant_batch 就是 t_plant种植的 batch
    @Query(value = "SELECT tpr.jy_name, tprd.reap_part herbal_part, tprd.batch `code`, tpr.begin_time start_time, tpr.end_time, tpr.plant_time, IF(tpr.reap_way = '机械', 2, 1) excavation_method, " +
            "tpr.reap_area area, tprd.reap_number amount FROM t_plant_reap tpr LEFT JOIN t_plant_reap_detail tprd ON tpr.id = tprd.reap_id " +
            "WHERE tpr.company_id = ?1 AND tpr.plant_batch = ?2", nativeQuery = true)
    List<JSONObject> dock3(String companyId, String plantBatch);


//    @Query(value = "SELECT tpr.jy_name, tprd.reap_part herbal_part, tprd.batch `code`, tpr.begin_time start_time, tpr.end_time, tpr.plant_time, IF(tpr.reap_way = '机械', 2, 1) excavation_method, " +
//            "tpr.reap_area area, tprd.reap_number amount FROM t_plant_reap tpr LEFT JOIN t_plant_reap_detail tprd ON tpr.id = tprd.reap_id " +
//            "WHERE tpr.company_id = ?1 AND tpr.plant_batch = ?2 LIMIT 1", nativeQuery = true)
//    JSONObject dock3(String companyId, String plantBatch);
}
